# -*- coding: utf-8 -*-
# @Time    : 2020/7/2 15:24
# @Author  : 会飞的明湖烤鸭
# @FileName: mysql_data_process.py
# @Software: PyCharm
import configparser
import os
import re
import datetime
from configparser import ConfigParser
import pymssql

conn = None
cursor = None


class sqlserver_data_process:
    def __init__(self):
        self.host = "127.0.0.1:1433"
        self.user = 'db_final'
        self.password = 'Czw2436111'
        self.database = "1_homework"
        self.charset = "utf8"
        self.connect_sqlserver()

    def connect_sqlserver(self):
        global conn
        global cursor
        conn = pymssql.connect(self.host, self.user, self.password, self.database, charset=self.charset)
        cursor = conn.cursor()
        if cursor:
            print("数据库连接成功")
        else:
            print("数据库连接失败")
        cursor.close()

    def select_all_from_table(self, table_name):
        try:
            cur = conn.cursor()
            sqlstr = "select * from " + table_name
            cur.execute(sqlstr)
            data = cur.fetchall()
            cur.close()
            print(data)
            return data
        except Exception as e:
            print(e, self.host)

    def run_store_produce(self, sp_exe_state):
        # global cursor
        try:
            cur = conn.cursor()
            cur.execute(sp_exe_state)
            result = cur.fetchall()
            conn.commit()
            cur.close()

            return result
        except Exception as e:
            # print(e, self.host)
            return e

    def run_buy(self, sp_exe_state):
        # global cursor
        try:
            cur = conn.cursor()
            cur.execute(sp_exe_state)
            result = cur.fetchall()
            # cur.close()

            return result
        except Exception as e:
            # print(e, self.host)
            return e

    def commit_item(self):
        conn.commit()

    def cancel_item(self):
        conn.rollback()

    def is_table_exists(self, table_name):
        global cursor
        sql = "show tables;"
        cursor.execute(sql)
        tables = [cursor.fetchall()]
        table_list = re.findall('(\'.*?\')', str(tables))
        table_list = [re.sub("'", '', each) for each in table_list]
        print(table_list)
        if table_name in table_list:
            return True
        else:
            return False

    def create_table(self, table_name):
        table_name = local_dict[table_name] + "_temperature"
        if self.is_table_exists(table_name) is not True:
            global cursor
            # 创建数据库SQL语句
            SQL = """CREATE TABLE {} (
             city  varchar(255),
             max_temperature  varchar(255),
             min_temperature  varchar(255),
             ave_temperature  varchar(255),
             precipitation    varchar(255),
             date varchar(255))""".format(table_name)
            cursor.execute(SQL)
            print("创建新表成功")
        else:
            print("创建失败")

    def change_manager_power(self, role_name, role1, role2, role3, change_user_index):
        global cursor
        global conn

        SQL = "select * from manager_info"
        cursor.execute(SQL)
        data = cursor.fetchall()

        SQL = "update manager_info set manager_name = '{}', " \
              "can_delete = '{}', can_add = '{}', can_look='{}' where manager_name = '{}' and can_delete = '{}' and " \
              "can_add = '{}' and can_look = '{}' ".format(role_name, role1, role2, role3,
                                                           data[int(change_user_index) - 1][0],
                                                           data[int(change_user_index) - 1][3],
                                                           data[int(change_user_index) - 1][4],
                                                           data[int(change_user_index) - 1][5])

        print(SQL)
        cursor.execute(SQL)
        conn.commit()
        return self

    def add_commit(self, user_name, mail, commit):
        global cursor
        global conn

        SQL = "select * from commit"
        cursor.execute(SQL)
        data = cursor.fetchall()

        SQL = "INSERT INTO commit(user_name, commit_time, commits, user_mail) VALUES (" \
              "'{}', '{}','{}','{}')".format(user_name, str(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')),
                                             commit, mail)
        print(SQL)
        cursor.execute(SQL)
        conn.commit()

    def delete_comment(self, index):
        global cursor
        global conn

        try:
            index -= 1
            SQL = "select * from commit"
            cursor.execute(SQL)
            data = cursor.fetchall()
            SQL = "delete from commit where user_name='{} 'and commits = '{}' and user_mail='{}'".format(data[index][0],
                                                                                                         data[index][2],
                                                                                                         data[index][3])
            print(data[index])
            cursor.execute(SQL)
            conn.commit()
            return True
        except Exception as e:
            return False


p = sqlserver_data_process()  # 创建一个对象
# p.select_all_from_table("shop")
# p.run_store_produce(" EXEC shop_log_in 'S20200001', 'Y20200279'")  # 调用存储过程
